<?php
    /**
 * Reports is a page that allows the generation of  reports by the director
 *
 * @Author Ryan Olson
 * @Version 1.0
 */

@session_start();
require_once 'shared-functions.php';
require_once 'session.php';
require_once 'masterpage.php';

//Make sure the user is logged in
if (!IsValidSession()) {
    header('Location: login.php?page=reports');
    exit();
}
else
{
    RefreshSession();
}

$user = GetCurrentUserAccessLevel();

//Check user access
if ($user != $DIRECTOR && $user != $ADMIN && $user != $PRINCIPLE && $user != $REGISTRAR) {
    header('Location: login.php?page=reports&error=To access to the reports page, please log in as a director, principle or admin&logout=1');
    exit();
}

masterpage("Reports");

?>

<form action="reports.php" method="post">
    <table>
        <tr>
            <td>
                <select name="report">
                    <option selected="true">Select report:</option>
                    <option>Total Student Debt</option>
                    <option>Total Student Debt (by program)</option>
                    <option>Avg Student Debt</option>
                    <option>DB Statistics</option>
                    <option>Top Student Debt</option>
					<option>List Students (by program)</option>
					<option>Total Fees Collected Today</option>
					<option>Total Fees Collected (by program)</option>
                </select>
            </td>
            <td>
                <input type="submit" value="Generate"/>
            </td>
        </tr>
    </table>
</form>

<?php

$table = "";
if (isset($_POST['report'])) {
    if ($_POST['report'] != "Select report:") {
        $link = connect_db();
        switch ($_POST['report'])
        {
            case "Total Student Debt":

                $query = "SELECT `Amount` FROM `FeeLedger`";
                $results = mysql_query($query, $link);
                if (!$results) {
                    echo 'Oops.. something went wrong.  Please contact support.' . mysql_error();
                    exit();
                }
                else
                {
                    $amount = 0;
                    while ($result = mysql_fetch_array($results, MYSQL_BOTH))
                    {
                        $amount += $result['Amount'];
                    }
                    $table = '<table class="DataList">
										<tr>
											<th class="header" align="center">Total Student Debt</th>
											<th class="header">&nbsp;</th>
										<tr>
										<tr>
											<td>
												' . $amount . ' GH&cent;
											</td>
											<td>
												&nbsp;
											</td>
										</tr>
									</table>';
                }
                break;

			case "Total Student Debt (by program)":
			
				$link = connect_db();
				$query = "SELECT `ProgramID`,`Name`,`Code`,`Year`,`Semester` FROM `Program`;";
				$result = mysql_query($query,$link);
				if(!$result)
				{
					echo 'Oops something went wrong.. please contact support.';
					exit;	
				}
				echo 'Program: ';
				echo '<form action="reports.php" method="post">';
				echo '<select name="ProgramID">';
				while($program=mysql_fetch_array($result,MYSQL_BOTH))
				{
					$selected = "";
					if(isset($_POST['ProgramID']) && $program['ProgramID'] == $_POST['ProgramID'])
					{
						$selected = ' selected="true" ';	
					}
					echo '<option value ="'.$program['ProgramID'].'" '.$selected.'>'.$program['Name'].', '.
																	   $program['Code'].'-'.
																	   str_pad($program['Semester'], 2, '0', STR_PAD_LEFT).'-'.
																	   $program['Year'].'</option>';
				}
				echo '</select>';
				echo '<input type="hidden" name="report" value="Total Student Debt (by program)" />';
				echo '<input type="submit" value="Choose" />';
				echo '</form>';
				
				$result = mysql_query($query,$link);
				$program = mysql_fetch_array($result, MYSQL_ASSOC);
				$ProgramID = $program['ProgramID'];
				
				if(isset($_POST['ProgramID']))
				{
					$ProgramID = $_POST['ProgramID'];
				}
				$query = "SELECT SUM(`Amount`) AS `Sum` FROM `FeeLedger` 
							INNER JOIN `Student` ON `Student`.`ID` = `FeeLedger`.`StudentID` WHERE `programID` = '".$ProgramID."'";
                $results = mysql_query($query, $link);
                if (!$results) {
                    echo 'Oops.. something went wrong.  Please contact support .' . mysql_error();
                    exit();
                }
                else
                {
                    $result = mysql_fetch_array($results, MYSQL_BOTH);
                    $amount = $result['Sum'];
					if($amount == "")
					{
                    	$amount = 0;
					}
                    $table = '<table class="DataList">
										<tr>
											<th class="header" align="center">Total Student Debt</th>
											<th class="header">&nbsp;</th>
										<tr>
										<tr>
											<td>
												' . $amount . ' GH&cent;
											</td>
											<td>
												&nbsp;
											</td>
										</tr>
									</table>';
					echo '<br />';
                }
				break;
            case "Avg Student Debt":

                $query = "SELECT `Amount` FROM `FeeLedger`";
                $results = mysql_query($query, $link);
                $query = "SELECT DISTINCT `StudentID` FROM `FeeLedger`";
                $avgresults = mysql_query($query, $link);
                if (!$results || !$avgresults) {
                    echo 'Oops.. something went wrong.  Please contact support.';
                    exit();
                }
                else
                {
                    $amount = 0;
                    while ($result = mysql_fetch_array($results, MYSQL_BOTH))
                    {
                        $amount += $result['Amount'];
                    }
                    $table = '<table class="DataList">
										<tr>
											<th class="header" align="center">Avg Student Debt</th>
										<tr>
										<tr>
											<td>
												' . round($amount / mysql_num_rows($avgresults), 2) . ' GH&cent;
											</td>
										</tr>
									</table>';
                }
                break;

            case "DB Statistics":

                //Get amount of total users
                $query = "SELECT COUNT(`UserId`) FROM `User`";
                $results = mysql_query($query, $link);
                if (!$results) {
                    echo 'Oops.. something went wrong.  Please contact support.';
                    exit();
                }
                $result = mysql_fetch_row($results);
                $userCount = $result[0];

                //Get amount of total students
                $query = "SELECT COUNT(`Id`) FROM `Student`";
                $results = mysql_query($query, $link);
                if (!$results) {
                    echo 'Oops.. something went wrong.  Please contact support.';
                    exit();
                }
                $result = mysql_fetch_row($results);
                $studentCount = $result[0];

                //Get amount of active users
                $query = "SELECT COUNT(`UserId`) FROM `User` WHERE `Active` = '1'";
                $results = mysql_query($query, $link);
                if (!$results) {
                    echo 'Oops.. something went wrong.  Please contact support.';
                    exit();
                }
                $result = mysql_fetch_row($results);
                $activeUsers = $result[0];

                //Get amount of inactive users
                $query = "SELECT COUNT(`UserId`) FROM `User` WHERE `Active` = '0'";
                $results = mysql_query($query, $link);
                if (!$results) {
                    echo 'Oops.. something went wrong.  Please contact support.';
                    exit();
                }
                $result = mysql_fetch_row($results);
                $inactiveUsers = $result[0];

                $table = '<table class="DataList">
										<tr>
											<th class="header" align="center" colspan="4">DB Statistics</th>
										<tr>
										<tr>
											<th class="header">User Count</th>
											<th class="header">Student Count</th>
											<th class="header">Active Users</th>
											<th class="header">Inactive Users</th>
										</tr>
										<tr>
											<td>
												' . $userCount . '
											</td>
											<td>
												' . $studentCount . '
											</td>
											<td>
												' . $activeUsers . '
											</td>
											<td>
												' . $inactiveUsers . '
											</td>
										</tr>
									</table>';

                break;
            case "Top Student Debt":
                    $table = '<table class="DataList">';
                    $table .= '  <thead>
                                <tr><th class="header" colspan="3">Top Student Debt</th></tr>
                                <tr><th class="header">StudentID</th>
                                <th class="header">Name</th>
                                <th class="header">Debt</th></tr>
                            </thead> ';
                    $link = connect_db();
                    $query = "SELECT `StudentID`, `FirstName`, `MiddleName`, `LastName`, SUM(`Amount`) AS `Total`
                                FROM `Student`
                                INNER JOIN `FeeLedger` ON `FeeLedger`.`StudentID` = `Student`.`ID`
                                INNER JOIN `User` ON `User`.`UserID` = `Student`.`ID`
                                GROUP BY `FeeLedger`.`StudentID`
                                ORDER BY `Total` DESC";
                    $results = mysql_query($query, $link);

                    $rowID = 0;
                    while($result = mysql_fetch_array($results, MYSQL_BOTH))
                    {
                        if ($rowID == 0)
                        {
                            $rowClass = "rows";
                            $rowID++;
                        }
                        else
                        {
                            $rowClass = "altrows";
                            $rowID--;
                        }

                        $table .= '<tr class="'.$rowClass.'">';
                            $table .= '<td class="'.$rowClass.'">'.$result['StudentID'].'</td>';
                            $table .= '<td class="'.$rowClass.'">'.$result['LastName'].', '.$result['FirstName'].' '.$result['MiddleName'].'</td>';
                            $table .= '<td class="'.$rowClass.'">'.$result['Total'].'</td>';
                        $table .= '</tr>';
                    }
                    $table .= '</table>';
                break;
				
			case "List Students (by program)":
				$link = connect_db();
				$query = "SELECT `ProgramID`,`Name`,`Code`,`Year`,`Semester` FROM `Program`;";
				$result = mysql_query($query,$link);
				if(!$result)
				{
					echo 'Oops something went wrong.. please contact support.';
					exit;	
				}
				echo 'Students by Program: ';
				echo '<form action="reports.php" method="post">';
				echo '<select name="ProgramID">';
				echo '<option value="-1">Choose a program:</option>';
				while($program=mysql_fetch_array($result,MYSQL_BOTH))
				{
					echo '<option value ="'.$program['ProgramID'].'">'.$program['Name'].', '.
																	   $program['Code'].'-'.
																	   str_pad($program['Semester'], 2, '0', STR_PAD_LEFT).'-'.
																	   $program['Year'].'</option>';
				}
				echo '</select>';
				echo '<input type="hidden" name="report" value="List Students (by program)" />';
				echo '<input type="submit" value="Choose" />';
				echo '</form>';
				
				if(isset($_POST['ProgramID']) && $_POST['ProgramID'] != -1)
				{
					$ProgramID = $_POST['ProgramID'];
	?>
					</form>
					<p/>
					<table class="DataList">
						<tr>
							<th class="header" colspan="6">Students by Program</th>
						</tr>
						<tr>
							<th class="header">Last Name</th>
							<th class="header">First Name</th>
							<th class="header">Middle Name</th>
							<th class="header">ID</th>
							<th class="header">School ID</th>
							<th class="header">Program</th>
						</tr>

					<?php
						//$link = connect_db();
						$query = "SELECT *
									FROM `User` 
									INNER JOIN `Student` ON `Student`.`ID`=`User`.`UserID`
									INNER JOIN `Program` ON `Program`.`ProgramID` = `Student`.`programID`";

						$query .= " WHERE `User`.`Active` = '1'";
						$query .= " AND `Program`.`ProgramID` = '$ProgramID'";
						$query .= " ORDER BY `LastName`, `FirstName`";

						$students = mysql_query($query, $link);
						if ($students === false) {
							echo "Oops...something went wrong. Please contact support.";
							exit();
						}

						if(mysql_num_rows($students) < 1)
						{
							echo '<tr><td class="rows">No students found.</td></tr>';
						}
						$rowID = 0;
						while ($student = mysql_fetch_array($students, MYSQL_BOTH))
						{
							if ($rowID == 0) {
								$rowClass = "rows";
								$rowID++;
							}
							else
							{
								$rowClass = "altrows";
								$rowID--;
							}
							
							echo'
								<tr>
								<td class="' . $rowClass . '">' . $student['LastName'] . '</td>
								<td class="' . $rowClass . '">' . $student['FirstName'] . '</td>
								<td class="' . $rowClass . '">' . $student['MiddleName'] . '</td>
								<td class="' . $rowClass . '">' . $student['UserId'] . '</td>
								<td class="' . $rowClass . '">' . $student['SchoolID'] . '</td>
								<td class="' . $rowClass . '">' . $student['Name'].', '.
																  $student['Code'].'-'.
																  str_pad($student['Semester'], 2, '0', STR_PAD_LEFT).
																  " ".$student['Year'] . '</td>
								</tr>';
						}
						
					echo '</table>';
				}
				
				break; // End of List Students (by program)
			
			case "Total Fees Collected Today":
				$today = date("Y-m-d");
				$link = connect_db();
				$query = "SELECT SUM(`Amount`) * -1 Total FROM `FeeLedger` WHERE `Amount` < 0 AND `Date` LIKE '%".$today."%'";
                $results = mysql_query($query, $link);
                if (!$results) {
                    echo 'Oops.. something went wrong.  Please contact support.' . mysql_error();
                    exit();
                }
				
				$fees = mysql_fetch_array($results, MYSQL_BOTH);
				
				$table = '<table class="DataList">
							<tr>
								<th class="header" align="center">Total Fees Collected Today: '.DisplayDate($today).'</th>
							<tr>
							<tr>
								<td>
									' . $fees['Total'] . ' GH&cent;
								</td>
							</tr>
						</table>';
			
				break; // End of Total Fees Collected Today
				
			case "Total Fees Collected (by program)":
				$link = connect_db();
				$query = "SELECT `ProgramID`,`Name`,`Code`,`Year`,`Semester` FROM `Program`;";
				$result = mysql_query($query,$link);
				if(!$result)
				{
					echo 'Oops something went wrong.. please contact support.';
					exit;	
				}
				echo 'Fees Collected by Program: ';
				echo '<form action="reports.php" method="post">';
				echo '<select name="ProgramID">';
				echo '<option value="-1">Choose a program:</option>';
				while($program=mysql_fetch_array($result,MYSQL_BOTH))
				{
					echo '<option value ="'.$program['ProgramID'].'">'.$program['Name'].', '.
																	   $program['Code'].'-'.
																	   str_pad($program['Semester'], 2, '0', STR_PAD_LEFT).'-'.
																	   $program['Year'].'</option>';
				}
				echo '</select>';
				echo '<input type="hidden" name="report" value="Total Fees Collected (by program)" />';
				echo '<input type="submit" value="Choose" />';
				echo '</form>';
				
				if(isset($_POST['ProgramID']) && $_POST['ProgramID'] != -1)
				{
					$ProgramID = $_POST['ProgramID'];
					
					$query = "SELECT `Name`, `Code`, `Year`, `Semester`, Sum(`Amount`) * -1 Total
								FROM `Program`
								INNER JOIN `Student` ON `Student`.`programID` = `Program`.`ProgramID`
								INNER JOIN `FeeLedger` ON `FeeLedger`.`StudentID` = `Student`.`ID`
									WHERE Amount < 0
									AND `Program`.`ProgramID` = '$ProgramID'
								GROUP BY `Name`, `Code`, `Year`, `Semester`";
					
					$results = mysql_query($query,$link);
					if(!$results)
					{
						echo 'Oops something went wrong.. please contact support.';
						echo mysql_error();
						exit;	
					}
					
					$fees = mysql_fetch_array($results, MYSQL_BOTH);
					
					$programName = $fees['Name'].", ".$fees['Code']."-".str_pad($fees['Semester'], 2, '0', STR_PAD_LEFT).'-'.$fees['Year'];
				
					$table = '<table class="DataList">
								<tr>
									<th class="header" align="center">Total Fees Collected (by program): '.$programName.'</th>
								<tr>
								<tr>
									<td>
										' . $fees['Total'] . ' GH&cent;
									</td>
								</tr>
							</table>';
				}
			
				break; // End of Total Fees Collected (by program)
        }
    }
}

if ($table != "")
    echo $table;

endmasterpage();
?>